package com.dfrz.bookstore.dao.impl;

import com.dfrz.bookstore.dao.Dao;
import com.dfrz.bookstore.entry.Admin;
import com.dfrz.bookstore.entry.Book;
import com.dfrz.bookstore.entry.User;
import com.dfrz.bookstore.util.JdbcUtils;

import javax.xml.crypto.Data;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 类功能描述:
 *
 * @Author keyan
 * @Date 2021/5/19 10:04
 */
public class BookDaoImpl implements Dao<Book> {
    @Override
    public boolean insert(Book book) {
        String sql = "insert into book (name,author,publisher,price,type_id,disc,discount,store,flag,img_src) values (?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement prep = null;
        Connection conn = null;
        try {
            conn = JdbcUtils.getConnection();
            prep = conn.prepareStatement(sql);
            prep.setString(1,book.getName());
            prep.setString(2,book.getAuthor());
            prep.setString(3,book.getPublisher());
            prep.setDouble(4,book.getPrice());
            prep.setInt(5,book.getType_id());
            prep.setInt(6,book.getDisc());
            prep.setDouble(7,book.getDiscount());
            prep.setInt(8,book.getStore());
            prep.setInt(9,book.getFlag());
            if (book.getImg_src()==null){
                book.setImg_src("https://keyantong.oss-cn-hangzhou.aliyuncs.com/book_img.jpeg");//设置默认图
            }
            prep.setString(10,book.getImg_src());
            int i = prep.executeUpdate();
            if (i>0){
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                JdbcUtils.close(conn,prep,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public boolean deleteObjById(int i) {
        String sql = "delete from book where id = "+i ;
        Connection conn = null;
        Statement stat = null;
        try {
            conn = JdbcUtils.getConnection();
            stat = conn.createStatement();
            int i1 = stat.executeUpdate(sql);
            if (i1>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return false;
    }

    @Override
    public boolean updateByObj(Book book) {
        if (book.getId()==0){
            return false;
        }
        StringBuilder sb = new StringBuilder("update book set id = "+book.getId());
        if (book.getName()!=null){
            sb.append(" , name = '"+book.getName()+"' ");
        }
        if (book.getAuthor()!=null){
            sb.append(" , author = '"+book.getAuthor()+"' ");
        }
        if (book.getPublisher()!=null){
            sb.append(" , publisher = '"+book.getPublisher()+"' ");
        }
        if (book.getPrice()!=0){
            sb.append(" , price = "+book.getPrice());
        }
        if (book.getImg_src()!=null){
            sb.append(" , img_src = '"+book.getImg_src()+"'");
        }
        if (book.getType_id()!=0){
            sb.append(" , type_id = " +book.getType_id());
        }
        if (true){
            sb.append(" , disc = "+book.getDisc());
        }
        if (book.getDiscount()!=0){
            sb.append(" , discount = "+book.getDiscount());
        }else {
            sb.append(" , discount = 10");  //不写折扣 默认原价
        }
        if (book.getStore()!=0){
            sb.append(" , store = "+book.getStore());
        }
        if (true){
            sb.append(" , flag = "+book.getFlag());
        }
        sb.append(" where id = "+book.getId());
        Connection conn = null;
        Statement stat = null;

        try {
            conn = JdbcUtils.getConnection();
            stat =  conn.createStatement();
            int i = stat.executeUpdate(sb.toString());
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                JdbcUtils.close(conn,stat,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public List<Book> findObjByObj(Book book) {
        List<Book> books = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from book where 1=1 ");
        if (book.getId()!= 0){
            sb.append(" and id = "+book.getId());
        }
        if (book.getName()!=null){
            sb.append(" and name like '%"+book.getName()+"%' ");
        }
        if (book.getAuthor()!=null){
            sb.append(" and author like '%"+book.getAuthor()+"%' ");
        }
        if (book.getPublisher()!=null){
            sb.append(" and publisher like '%"+book.getPublisher()+"%' ");
        }
        if (book.getPrice()!=0){
            sb.append(" and price = "+book.getPrice());
        }
        if (book.getType_id()!=0){
            sb.append(" and type_id = "+book.getType_id());
        }
        if (book.getDisc()!=0){
            sb.append(" and disc = "+book.getDisc());  //有问题
        }
        if (book.getStore()!=0){
            sb.append(" and store = "+book.getStore());    //有问题
        }
        if (book.getFlag()!=0){
            sb.append(" and flag = "+book.getFlag());  //有问题
        }
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;

        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                Book book1 = new Book();
                book1.setId(resultSet.getInt("id"));
                book1.setName(resultSet.getString("name"));
                book1.setAuthor(resultSet.getString("author"));
                book1.setPublisher(resultSet.getString("publisher"));
                book1.setImg_src(resultSet.getString("img_src"));
                book1.setPrice(resultSet.getDouble("price"));
                book1.setType_id(resultSet.getInt("type_id"));
                book1.setDisc(resultSet.getInt("disc"));
                book1.setDiscount(resultSet.getDouble("discount"));
                book1.setStore(resultSet.getInt("store"));
                book1.setFlag(resultSet.getInt("flag"));
                book1.setCreate_time(resultSet.getDate("create_update"));
                books.add(book1);
            }
            /*返回查找到的书本*/
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                JdbcUtils.close(conn,stat,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public List<Book> findObjByPage(Book book,int pageNum, int pageSize)  {
        int start = (pageNum-1)*pageSize;
        List<Book> books = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from book where 1 = 1 ");

        if (book.getId()!=0){
            sb.append(" and id = "+book.getId());
        }
        if (book.getName()!=null){
            sb.append(" and name like '%"+book.getName()+"%' ");
        }
        if (book.getAuthor()!=null){
            sb.append(" and author like '%"+book.getAuthor()+"%' ");
        }
        if (book.getPublisher()!=null){
            sb.append(" and publisher like '%"+book.getPublisher()+"%' ");
        }
        if (book.getPrice()!=0){
            sb.append(" and price = '"+book.getPrice()+"' ");
        }
        if (book.getType_id()!=0){
            sb.append(" and type_id = '"+book.getType_id()+"' ");
        }
        if (book.getDisc()!=0){
            sb.append(" and disc = '"+book.getDisc()+"' ");  //有问题
        }
        if (book.getStore()!=0){
            sb.append(" and store'"+book.getStore()+"' ");    //有问题
        }
        if (book.getFlag()!=0){
            sb.append(" and flag = '"+book.getFlag()+"' ");  //有问题
        }
        sb.append(" limit "+ start +","+pageSize);
//        System.out.println(sb.toString());
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                Book book1 = new Book();
                book1.setId(resultSet.getInt("id"));
                book1.setName(resultSet.getString("name"));
                book1.setAuthor(resultSet.getString("author"));
                book1.setPublisher(resultSet.getString("publisher"));
                book1.setImg_src(resultSet.getString("img_src"));
                book1.setPrice(resultSet.getDouble("price"));
                book1.setType_id(resultSet.getInt("type_id"));
                book1.setDisc(resultSet.getInt("disc"));
                book1.setDiscount(resultSet.getDouble("discount"));
                book1.setStore(resultSet.getInt("store"));
                book1.setFlag(resultSet.getInt("flag"));
                book1.setCreate_time(resultSet.getDate("create_update"));
                books.add(book1);
            }
            /*返回分页查找到的书本*/
            return books;
            } catch (SQLException e) {
                e.printStackTrace();
            }
                finally {
                try {
                    JdbcUtils.close(conn,stat,resultSet);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
                return null;
            }


     public int findCount(Book book,Object object){
         String sql =  "select count(name) from book where 1=1 ";
         //判断是用户还是管理员
         if (object instanceof User){
//             System.out.println("用户");
             sql+=" and `flag` = 1";
         }
         //判断是否需要查找书名
         if (book.getName()!=null) {
             sql += " and name like '%" + book.getName() + "%' ";
         }
         if (book.getType_id()!=0){
             sql += " and type_id =  "+ book.getType_id();
         }
//         System.out.println(sql);
         Connection conn = null;
         Statement stat = null;
         ResultSet resultSet = null;
         try {
             conn = JdbcUtils.getConnection();
             stat = conn.createStatement();
             resultSet = stat.executeQuery(sql);
             resultSet.next();
             int count = resultSet.getInt("count(name)");
             return count;
         } catch (SQLException e) {
             e.printStackTrace();
         }finally {
             try {
                 JdbcUtils.close(conn,stat,resultSet);
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
        return 0;
     }
}
